import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
from datetime import datetime


app = Flask(__name__, template_folder="templates", static_folder="static")
# 连接数据库连接url
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://zhangyz:123456@10.4.7.13:3306/school?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
# 查询时会显示原始SQL语句
app.config["SQLALCHEMY_ECHO"] = True

# 把SQLAlchemy组件注册到项目中
db = SQLAlchemy()
db.init_app(app)


class StudentCourse(db.Model):
    __tablename__ = "table_nvm_student_course"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    sid = db.Column(db.Integer, db.ForeignKey("table_nvm_student.id"), comment="学生ID")
    cid = db.Column(db.Integer, db.ForeignKey("table_nvm_course.id"), comment="课程ID")
    create_time = db.Column(db.DateTime, default=datetime.now, comment="创建时间")

    # 关联属性
    student = db.relationship("Student", uselist=False, backref=backref("to_relation", uselist=True))
    course = db.relationship("Course", uselist=False, backref=backref("to_relation", uselist=True))

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"

class Student(db.Model):
    """学生信息模型"""
    __tablename__ = "table_nvm_student"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    name = db.Column(db.String(15), index=True, comment="姓名")
    age = db.Column(db.SmallInteger, comment="年龄")
    sex = db.Column(db.Boolean, default=True, comment="性别")
    money = db.Column(db.Numeric(10, 2), default=0.0, comment="钱包")

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"

class Course(db.Model):
    """课程信息模型"""
    __tablename__ = "table_nvm_course"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    name = db.Column(db.String(255), unique=True, comment="课程名称")

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"

@app.route("/add_data01")
def add_data01():
    """添加数据"""
    # 添加其中一个主模型数据时，同时绑定添加另外一个主模型的数据
    student = Student(
        name="张三",
        age=18,
        sex=True,
        money=10000,
        to_relation=[
            StudentCourse(course=Course(name="Python入门")),
            StudentCourse(course=Course(name="Python初级")),
            StudentCourse(course=Course(name="Python进阶")),
        ]
    )
    db.session.add(student)
    db.session.commit()

    return "ok"


@app.route("/add_data02")
def add_data02():
    student = Student.query.filter(Student.name=="张三").first()
    student.to_relation.extend([
        # 已经存在的可成，给学生报读
        StudentCourse(course=Course.query.get(1)),
        # 新增可成，并让当前学生报读该课程
        StudentCourse(course=Course(name="Python高级")),
    ])
    db.session.commit()
    return "ok"

@app.route("/add_data03")
def add_data03():
    # 已有学生和课程，对学生购买课程进行记录
    student = Student.query.get(1)
    course_list = Course.query.filter(Course.id.in_([2, 3])).all()
    student.to_relation.extend([StudentCourse(course=course) for course in course_list])
    db.session.commit()

    return "ok"

@app.route("/query_data01")
def query_data01():
    # 查询学生购买的课程
    student = Student.query.get(1)
    print([relation.course for relation in student.to_relation])
    return "ok"

@app.route("/query_data02")
def query_data02():
    # 查询指定可成有哪些学生购买了
    course = Course.query.get(1)
    print([relation.student for relation in course.to_relation])
    return "ok"

@app.route("/query_data03")
def query_data03():
    # 查询2号学生购买的每个可成的时间
    student = Student.query.get(2)
    for relation in student.to_relation:
        print(relation.course.time)

    return "ok"

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(host='0.0.0.0', port=5000, debug=True)